﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DotNetDal.Core.Data
{
    public class DALTable : DALRecord
    {
        private void _insert()
        {
            string sql = "insert into [" + SchemaName + "].[" + ClassName + "]";
            string names = "";
            string values = "";
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            foreach (string fieldName in this.FieldNames)
            {
                if (!IsAutoNumber(fieldName) && !IsPrimaryKey(fieldName))
                {
                    names += (names != "" ? " , " : "") + "[" + fieldName + "]";
                    values += (values != "" ? " , " : "") + "@" + fieldName;

                    SqlParameter param = new SqlParameter();
                    param.Direction = ParameterDirection.Input;
                    param.DbType = GetDatabaseType(fieldName);
                    param.Value = this.Fields[fieldName] == null ? DBNull.Value : this.Fields[fieldName];
                    param.ParameterName = fieldName;

                    sqlParams.Add(param);
                }
            }

            sql += " ( " + names + " ) values ( " + values + " ); ";
            sql += " SET @IdentityId = SCOPE_IDENTITY(); ";

            using (SqlConnection conn = SqlServer.GetConnection())
            {
                using (SqlCommand insertCommand = conn.CreateCommand())
                {
                    SqlParameter identityParam = new SqlParameter("@IdentityId", SqlDbType.Int);
                    identityParam.Direction = ParameterDirection.Output;

                    insertCommand.CommandText = sql;
                    insertCommand.Parameters.AddRange(sqlParams.ToArray());
                    insertCommand.Parameters.Add(identityParam);
                    insertCommand.Connection.Open();
                    insertCommand.ExecuteNonQuery();

                    if (identityParam.Value != System.DBNull.Value)
                    {
                        int i = (int)identityParam.Value;

                        foreach (string fieldName in this.FieldNames)
                        {
                            if (IsPrimaryKey(fieldName))
                            {
                                this.Fields[fieldName] = i;
                                break;
                            }
                        }
                    }
                }
            }
        }
        private void _update()
        {
            string updateSql = "";
            string whereSql = "";
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            string sql = "update [" + SchemaName + "].[" + ClassName + "]";

            foreach (string fieldName in this.FieldNames)
            {
                if (IsPrimaryKey(fieldName))
                {
                    whereSql += (whereSql != "" ? " and " : "") + "[" + fieldName + "] = @" + fieldName;

                    SqlParameter param = new SqlParameter();
                    param.Direction = ParameterDirection.Input;
                    param.DbType = GetDatabaseType(fieldName);
                    param.Value = this.Fields[fieldName] == null ? DBNull.Value : this.Fields[fieldName];
                    param.ParameterName = fieldName;
                    
                    sqlParams.Add(param);
                }
                else if (!IsAutoNumber(fieldName))
                {
                    updateSql += (updateSql != "" ? " , " : "") + "[" + fieldName + "] = @" + fieldName;

                    SqlParameter param = new SqlParameter();
                    param.Direction = ParameterDirection.Input;
                    param.DbType = GetDatabaseType(fieldName);
                    param.Value = this.Fields[fieldName] == null ? DBNull.Value : this.Fields[fieldName];
                    param.ParameterName = fieldName;

                    sqlParams.Add(param);
                }
            }

            sql += " set " + updateSql + " where " + whereSql;

            SqlServer.ExecuteNonQuery(sql, sqlParams.ToArray());
        }

        public void Save()
        {
            FieldList keys = new FieldList();

            if (RetrieveFromDatabase) //update
            {
                _update();
            }
            else //insert
            {
                _insert();
            }
        }
        public void Delete()
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            string sql = "delete from [" + SchemaName + "].[" + ClassName + "] where ";
            string list = "";

            foreach (string fieldName in this.FieldNames)
            {
                if (IsPrimaryKey(fieldName))
                {
                    list += (list != "" ? " and " : "") + "[" + fieldName + "] = @" + fieldName;
                    
                    SqlParameter param = new SqlParameter();
                    param.Direction = ParameterDirection.Input;
                    param.DbType = GetDatabaseType(fieldName);
                    param.Value = this.Fields[fieldName] == null ? DBNull.Value : this.Fields[fieldName];
                    param.ParameterName = fieldName;

                    sqlParams.Add(param);
                }
            }

            sql += " " + list;

            SqlServer.ExecuteNonQuery(sql, sqlParams.ToArray());
        }
    }
}
